package com.augurit.gzcgwsp.common.exp;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
   
/**   
* POI操作Excel   
* @since: 1.0   
* @version:1.0   
* @createTime:2011年05月21日 16：12：15   
* @updateTime:2011年07月21日 10：47：20   
* @author 
*/   
public class PoiExcelSheetCopy {    
        
    /** 
     * 复制单个文件 
     * @param oldPath String 原文件路径 如：c:/fqf.txt 
     * @param newPath String 复制后路径 如：f:/fqf.txt 
     * @return boolean 
     */ 
   public static boolean copyFile(String oldPath, String newPath) { 
       try { 
           int bytesum = 0; 
           int byteread = 0; 
           File oldfile = new File(oldPath); 
           if (oldfile.exists()) { //文件存在时 
               InputStream inStream = new FileInputStream(oldPath); //读入原文件 
               FileOutputStream fs = new FileOutputStream(newPath); 
               byte[] buffer = new byte[1444]; 
               int length; 
               while ( (byteread = inStream.read(buffer)) != -1) { 
                   bytesum += byteread; //字节数 文件大小 
                   System.out.println(bytesum); 
                   fs.write(buffer, 0, byteread); 
               } 
               inStream.close(); 
               fs.close();
           } 
           return true;
       } 
       catch (Exception e) { 
           System.out.println("复制单个文件操作出错"); 
           e.printStackTrace(); 
           return false;
       } 
   } 
    
    /**   
     * 根据源Sheet样式copy新Sheet   
     * @param fromsheetname   
     * @param newsheetname   一个sheet中名称不能相同
     * @param targetFile   
     */   
    public static void copySheet(String fromsheetname, String newsheetname,String targetFile) {    
        HSSFWorkbook wb = null;    
        HSSFSheet newsheet = null;
        try {    
            FileInputStream fis = new FileInputStream(targetFile);    
            wb = new HSSFWorkbook(fis);    
            HSSFSheet fromsheet = wb.getSheet(fromsheetname);    
            if (fromsheet != null && wb.getSheet(newsheetname) == null) {    
                newsheet = wb.createSheet(newsheetname);    
                //设置打印参数    
                newsheet.setMargin(HSSFSheet.TopMargin,fromsheet.getMargin(HSSFSheet.TopMargin));// 页边距（上）    
                newsheet.setMargin(HSSFSheet.BottomMargin,fromsheet.getMargin(HSSFSheet.BottomMargin));// 页边距（下）    
                newsheet.setMargin(HSSFSheet.LeftMargin,fromsheet.getMargin(HSSFSheet.LeftMargin) );// 页边距（左）    
                newsheet.setMargin(HSSFSheet.RightMargin,fromsheet.getMargin(HSSFSheet.RightMargin));// 页边距（右    
                    
                HSSFPrintSetup ps = newsheet.getPrintSetup();    
                ps.setLandscape(false); // 打印方向，true：横向，false：纵向(默认)    
                ps.setVResolution((short)600);    
                ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张类型    
                    
                File file = new File(targetFile);    
                if (file.exists() && (file.renameTo(file))) {    
                    copyRows(wb, fromsheet, newsheet, fromsheet.getFirstRowNum(), fromsheet.getLastRowNum());    
                    FileOutputStream fileOut = new FileOutputStream(targetFile);    
                    wb.write(fileOut);    
                    fileOut.flush();    
                    fileOut.close();    
                } else {    
                    System.out.println("文件不存在或者正在使用,请确认…");    
                }    
                fis.close();
            }    
            fis.close();    
        } catch (Exception e) {    
            e.printStackTrace();    
        }  finally {
            //return newsheet;
        }  
    }    
        
    /**   
     * 拷贝Excel行   
     * @param wb   
     * @param fromsheet   
     * @param newsheet   
     * @param firstrow   
     * @param lastrow   
     */   
    public static void copyRows(HSSFWorkbook wb, HSSFSheet fromsheet,HSSFSheet newsheet, int firstrow, int lastrow) {    
        if ((firstrow == -1) || (lastrow == -1) || lastrow < firstrow) {    
            return;    
        }    
        // 拷贝合并的单元格    
        Region region = null;    
        for (int i = 0; i < fromsheet.getNumMergedRegions(); i++) {    
            region = fromsheet.getMergedRegionAt(i);    
            if ((region.getRowFrom() >= firstrow)&& (region.getRowTo() <= lastrow)) {    
                newsheet.addMergedRegion(region);    
            }    
        }    
   
        HSSFRow fromRow = null;    
        HSSFRow newRow = null;    
        HSSFCell newCell = null;    
        HSSFCell fromCell = null;    
        // 设置列宽    
        for (int i = firstrow; i <= lastrow; i++) {    
            fromRow = fromsheet.getRow(i);    
            if (fromRow != null) {    
                for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {    
                    int colnum = fromsheet.getColumnWidth((short) j);    
                    if (colnum > 100) {    
                        newsheet.setColumnWidth((short) j, (short) colnum);    
                    }    
                    if (colnum == 0) {    
                        newsheet.setColumnHidden((short) j, true);    
                    } else {    
                        newsheet.setColumnHidden((short) j, false);    
                    }    
                }    
                break;    
            }    
        }    
        // 拷贝行并填充数据    
        for (int i = 0; i <= lastrow; i++) {    
            fromRow = fromsheet.getRow(i);    
            if (fromRow == null) {    
                continue;    
            }    
            newRow = newsheet.createRow(i - firstrow);
            newRow.setHeight(fromRow.getHeight());    
            for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) {    
                fromCell = fromRow.getCell((short) j);    
                if (fromCell == null) {    
                    continue;    
                }    
                newCell = newRow.createCell((short) j);    
                newCell.setCellStyle(fromCell.getCellStyle());    
                int cType = fromCell.getCellType();    
                newCell.setCellType(cType);    
                switch (cType) {    
                case HSSFCell.CELL_TYPE_STRING:    
                    newCell.setCellValue(fromCell.getRichStringCellValue());    
                    break;    
                case HSSFCell.CELL_TYPE_NUMERIC:    
                    newCell.setCellValue(fromCell.getNumericCellValue());    
                    break;    
                case HSSFCell.CELL_TYPE_FORMULA:    
                    newCell.setCellFormula(fromCell.getCellFormula());    
                    break;    
                case HSSFCell.CELL_TYPE_BOOLEAN:    
                    newCell.setCellValue(fromCell.getBooleanCellValue());    
                    break;    
                case HSSFCell.CELL_TYPE_ERROR:    
                    newCell.setCellValue(fromCell.getErrorCellValue());    
                    break;    
                default:    
                    newCell.setCellValue(fromCell.getRichStringCellValue());    
                    break;    
                }    
            }    
        }    
    }    
    
    /**
     * 删除临时文件方法
     * @param file
     * @return
     */
    public static boolean delFile(File file){
        if (!file.exists()) {
        System.out.println("文件不存在");
        return false;
        }
        boolean rs = file.delete();
        if (rs){
            System.out.println("文件删除成功");
            return true;
        }
        else{
            System.out.println("文件删除失败");
            return false;
        }
    }
    
    public static void main(String[] args) {    
        
        String filePath = "D:/downloadtest/20110915184913288686.xls";
        File file = new File(filePath);
        delFile(file);
        
        /** copy    sheet 方法 */
//        FileInputStream fis = null;
//        try {
//            PoiExcelSheetCopy ew = new PoiExcelSheetCopy();    
//            fis = new FileInputStream("d:/template4.xls");    
//            HSSFWorkbook wb = new HSSFWorkbook(fis);    
//            for(int i = 0; i < 5; i++){
//                HSSFSheet newSheet = ew.copySheet("template", "test3"+i, "d:/template4.xls");
//                String sheetName = newSheet.getSheetName();
//                System.out.println(sheetName);
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }finally{
//            try {
//                if(fis != null){
//                    fis.close();
//                }
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//            
//        }
        
        /** copy    文件 方法 */
//        String aa = "d:/exptest.xls";
//        String bb = "d:/exptest1.xls";
//        boolean cc = PoiExcelSheetCopy.copyFile(aa, bb);
//        System.out.println(cc);
        
    }    
}    
